<?php include_once "../ra_global.php";
header('Content-Type: text/html; charset=gb2312');
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/vnd.ms-excel');

//总体数据
if($_GET['t']==1){
	$filename='总体数据';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>城市</th>
				<th>允许开通数</th>
				<th>已开通数</th>
				<th>总店员数</th>
				<th>满员店铺数</th>
			</tr>
	';
	foreach($db->ig2_select('mae_city','1') as $list){
		$city_shops=$db->ig2_select('mae_shop','city_id='.$list['id'].' and is_activate=1');
		$num1=$num2=0;
		foreach($city_shops as $shop){
			
			$roles=count($db->ig2_select('mae_role','shop_id='.$shop['id'].' and is_delete=0 and role_type!=1'));
			$num1+=$roles;
			if($roles >= 5) $num2++;
			
		}
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$list['name'].'</td>
				<td>'.$list['limit'].'</td>
				<td>'.(count($city_shops)).'</td>
				<td>'.$num1.'</td>
				<td>'.$num2.'</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//兑换记录
if($_GET['t']==2){
	$filename='兑换记录';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$where='1';
	if($stime && $etime) $where.=(' and create_time >= '.$stime.' and create_time <= '.$etime);
	$where.=' order by id desc';
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>店员姓名</th>
				<th>联系电话</th>
				<th>地址</th>
				<th>当前兑换积分</th>
				<th>大师积分</th>
				<th>兑换记录</th>
				<th>兑换时间</th>
			</tr>
	';
	foreach($db->ig2_select('mae_shop_user_exchange_list',$where) as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$prize=$db->ig2_want('mae_prize','id='.$list['prize_id']);
		$role=$db->ig2_want('mae_role','uid='.$user['id']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$city['name'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.($user?$user['name']:'该店员已被删除').'</td>
				<td>'.($user?$user['phone']:'该店员已被删除').'</td>
				<td>'.($user?$user['address']:'该店员已被删除').'</td>
				<td>'.$prize['need_integral'].'</td>
				<td>'.($user?$user['score']:0).'</td>
				<td>'.$prize['name'].'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//门店详细
if($_GET['t']==3){
	$filename='门店详细';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$num=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>店铺编码</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>管理员姓名</th>
				<th>管理员手机</th>
				<th>管理员地址</th>
				<th>店员数量</th>
				<th>总销售量</th>
				<th>月销售量</th>
				<th>月销售量排名</th>
				<th>月增长率(%)</th>
				<th>月增长率排名</th>
			</tr>
	';
	$shops=$db->ig2_select('mae_shop','is_activate=1 order by city_id asc');
	$sale_arr=array();
	$growth_arr=array();
	foreach($shops as $shop){
		$sale_arr[$shop['id']]=$shop['now_month_count'];
		$growth_arr[$shop['id']]=($shop['now_month_count']-$shop['prev_month_count'])/$shop['prev_month_count']*100;
	}
	arsort($sale_arr);
	arsort($growth_arr);
	foreach($shops as $list){
		$i=1;
		foreach($sale_arr as $k=>$v){
			if($k==$list['id']) break;
			$i++;
		}
		$j=1;
		foreach($growth_arr as $k=>$v){
			if($k==$list['id']) break;
			$j++;
		}
		$city=$db->ig2_want('mae_city','id='.$list['city_id']);
		$admin=$db->ig2_want('mae_user','id='.$list['admin_id']);
		$users=$db->ig2_query_select('select count(*) as total from mae_role where shop_id='.$list['id'].' and role_type!=1');
		$growth=($list['now_month_count']-$list['prev_month_count'])/$list['prev_month_count']*100;
		$shop_old=$db->ig2_want('mae_shop_orginal_unique','tid='.$list['tid']);
		$str.='
			<tr>
				<td>'.$num.'</td>
				<td>'.$shop_old['code'].'</td>
				<td>'.$city['name'].'</td>
				<td>'.$list['name'].'</td>
				<td>'.$admin['name'].'</td>
				<td>'.$admin['phone'].'</td>
				<td>'.$admin['address'].'</td>
				<td>'.$users[0]['total'].'</td>
				<td>'.$list['sell_count'].'</td>
				<td>'.$list['now_month_count'].'</td>
				<td>'.($list['now_month_count']?$i:'暂无').'</td>
				<td>'.($growth?$growth:'暂无').'</td>
				<td>'.($growth?$j:'暂无').'</td>
			</tr>
		';
		$num++;
	}
	$str.='</table>';
	echo $str;
}

//销售记录
if($_GET['t']==4){
	$filename='销售记录';
	//header('Content-Disposition: filename='.$filename.'.xls');
	header('Content-type: text/html; charset=utf-8');
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$where='1';
	if($stime && $etime) $where.=(' and create_time >= '.$stime.' and create_time <= '.$etime);
	$where.=' order by create_time desc';
	
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>店员姓名</th>
				<th>店员手机号</th>
				<th>店员地址</th>
				<th>防伪码</th>
				<th>产品号</th>
				<th>产品名称</th>
				<th>产品简介</th>
				<th>销售时间</th>
			</tr>
	';
	foreach($db->ig2_select('mae_sell',$where) as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$role=$db->ig2_want('mae_role','uid='.$list['uid']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$city['name'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.($user?$user['name']:'该店员已被删除').'</td>
				<td>'.($user?$user['phone']:'该店员已被删除').'</td>
				<td>'.($user?$user['address']:'该店员已被删除').'</td>
				<td>油品编码：'.($list['shell_key']).'</td>
				<td>'.$list['pro_id'].'</td>
				<td>'.$list['productname'].'</td>
				<td>'.$list['productspec'].'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//心愿单数据
if($_GET['t']==5){
	$filename='心愿单数据';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$where='1';
	if($stime && $etime) $where.=(' and create_time >= '.$stime.' and create_time <= '.$etime);
	$where.=' order by create_time desc';
	
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>店员姓名</th>
				<th>店员手机号</th>
				<th>店员地址</th>
				<th>店员心愿</th>
				<th>提交时间</th>
			</tr>
	';
	foreach($db->ig2_select('mae_wish_list',$where) as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$role=$db->ig2_want('mae_role','uid='.$list['uid']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$city['name'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.($user?$user['name']:'该店员已被删除').'</td>
				<td>'.($user?$user['phone']:'该店员已被删除').'</td>
				<td>'.($user?$user['address']:'该店员已被删除').'</td>
				<td>'.$list['content'].'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//答题数据
if($_GET['t']==6){
	$filename='答题数据';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$where='1';
	if($stime && $etime) $where.=(' and create_time >= '.$stime.' and create_time <= '.$etime);
	$where.=' order by create_time desc';
	
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>店员姓名</th>
				<th>店员手机号</th>
				<th>店员地址</th>
				<th>答题题目</th>
				<th>答题结果</th>
				<th>答题时间</th>
			</tr>
	';
	$num_1=$num_2=$num_3=0;
	foreach($db->ig2_select('mea_user_answer_record',$where) as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$role=$db->ig2_want('mae_role','uid='.$list['uid']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		$question=$db->ig2_want('mae_question','id='.$list['qid']);
		switch($list['result']){
			case 0 : $result='错误'; $num_3++; break;
			case 1 : $result='正确'; $num_2++; break;
		}
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$city['name'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.($user?$user['name']:'该店员已被删除').'</td>
				<td>'.($user?$user['phone']:'该店员已被删除').'</td>
				<td>'.($user?$user['address']:'该店员已被删除').'</td>
				<td>'.$question['content'].'</td>
				<td>'.$result.'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$num_1++;
		$i++;
	}
	$str.='<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>';
	$str.='
		<tr>
			<td></td>
			<td></td>
			<td></td>
			<th>总计：</th>
			<td>总答题数：'.$num_1.'</td>
			<td>答题正确数：'.$num_2.'</td>
			<td>答题错误数：'.$num_3.'</td>
			<td>答题获得积分数：'.($num_2*10).'分</td>
		</tr>
	';
	$str.='</table>';
	echo $str;
}

//意见箱
if($_GET['t']==7){
	$filename='意见箱';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$i=1;
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>店铺编码</th>
				<th>城市</th>
				<th>门店名称</th>
				<th>店员姓名</th>
				<th>店员手机号</th>
				<th>店员地址</th>
				<th>内容</th>
				<th>提交时间</th>
			</tr>
	';
	
	foreach($db->ig2_select('mae_opnion','1 order by create_time desc') as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$role=$db->ig2_want('mae_role','uid='.$list['uid']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$shop_old=$db->ig2_want('mae_shop_orginal_unique','tid='.$shop['tid']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$shop_old['code'].'</td>
				<td>'.$city['name'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.($user?$user['name']:'该店员已被删除').'</td>
				<td>'.($user?$user['phone']:'该店员已被删除').'</td>
				<td>'.($user?$user['address']:'该店员已被删除').'</td>
				<td>'.$list['content'].'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//城市销量排名
if($_GET['t']==8){
	$filename='城市销量排名';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$str='
		<table>
			<tr>
				<th>排名</th>
				<th>城市</th>
				<th>销量</th>
				<th>店铺活跃数</th>
				<th>店铺活跃度</th>
				<th>机修工活跃数</th>
				<th>机修工活跃度</th>
			</tr>
	';
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$res=$db->ig2_query_select('select `mae_city`.name,`mae_city`.id,count(*) as count from `mae_city`,`mae_shop`,`mae_role`,`mae_sell` where `mae_sell`.uid=`mae_role`.uid and `mae_role`.shop_id=`mae_shop`.id and `mae_shop`.city_id=`mae_city`.id and `mae_sell`.create_time >= '.$stime.' and `mae_sell`.create_time <= '.$etime.' group by `mae_city`.name order by count desc');
	
	$city_id=array();
	foreach($res as $k=>$v){
		$city_id[]=$v['id'];
		
		
		$city_id[]=$v['id'];
		$shop_all=$db->ig2_query_select('select id from mae_shop where city_id='.$v['id'].' and is_activate=1');
		$shop_all_count=count($shop_all);//城市下店铺已激活总数
		$shop_id=array();
		for($i=0;$i<$shop_all_count;$i++){
			$shop_id[]=$shop_all[$i]['id'];
		}
		$shop_sell=$db->ig2_query_select('select uid from mae_sell where uid in(select uid from mae_role where shop_id in('.implode(',',$shop_id).') and is_delete=0 and role_type!=1) and create_time >= '.$stime.' and create_time <= '.$etime.' group by uid');
		
		$shop_id_new=array();
		for($i=0;$i<count($shop_sell);$i++){
			$sid=$db->ig2_want('mae_role','uid='.$shop_sell[$i]['uid'].' and is_delete=0');
			if(!in_array($sid['shop_id'],$shop_id_new)){
				$shop_id_new[]=$sid['shop_id'];
			}
		}
		
		$shop_sell_count=count($shop_id_new);//城市下店铺活跃数
		$shop_activity_degree=ceil($shop_sell_count/$shop_all_count*10000)/100;//城市下店铺活跃度
		
		
		$user_all=$db->ig2_query_select('select uid from mae_role where shop_id in('.implode(',',$shop_id).') and is_delete=0 and role_type!=1');
		$user_all_count=count($user_all);//城市下机修工总数
		$user_id=array();
		for($i=0;$i<$user_all_count;$i++){
			$user_id[]=$user_all[$i]['uid'];
		}
		$user_sell=$db->ig2_query_select('select uid from mae_sell where uid in('.implode(',',$user_id).') and create_time >= '.$stime.' and create_time <= '.$etime.' group by uid');
		$user_sell_count=count($user_sell);//城市下机修工活跃数
		$user_activity_degree=ceil($user_sell_count/$user_all_count*10000)/100;//城市下机修工活跃度
		//echo '店铺活跃度'.$shop_activity_degree.'机修工活跃度'.$user_activity_degree.'<br>';
		
		$str.='
			<tr>
				<td>'.($k+1).'</td>
				<td>'.$v['name'].'</td>
				<td>'.$v['count'].'</td>
				<td>'.$shop_sell_count.'</td>
				<td>'.$shop_activity_degree.'%</td>
				<td>'.$user_sell_count.'</td>
				<td>'.$user_activity_degree.'%</td>
			</tr>
		';
	}
	
	$i=1;
	$count_res=count($res);
	$city_where='1';
	$city_where.=$city_id?(' and id not in('.implode(',',$city_id).')'):'';
	foreach($db->ig2_select('mae_city',$city_where) as $list){
		$str.='
			<tr>
				<td>'.($count_res+$i).'</td>
				<td>'.$list['name'].'</td>
				<td>0</td>
				<td>0</td>
				<td>0</td>
				<td>0</td>
				<td>0</td>
			</tr>
		';
		$i++;
	}
	$str.='</table>';
	echo $str;
}

//店铺销量排名
if($_GET['t']==9){
	$filename='店铺销量排名';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$str='
		<table>
			<tr>
				<th>排名</th>
				<th>店铺编码</th>
				<th>城市</th>
				<th>店名</th>
				<th>店长</th>
				<th>电话</th>
				<th>地址</th>
				<th>销量</th>
			</tr>
	';
	
	$stime=strtotime($_GET['sdate'].' '.$_GET['stime']);
	$etime=strtotime($_GET['edate'].' '.$_GET['etime']);
	$res=$db->ig2_query_select('SELECT `mae_shop`.tid as stid, `mae_shop`.name as sname, `mae_shop`.id as sid, `mae_city`.name as cname, `mae_user`.name as uname, `mae_user`.phone as uphone, `mae_user`.address as uaddress, count(*) as count FROM `mae_shop`, `mae_city`, `mae_user`, `mae_role`, `mae_sell` WHERE `mae_sell`.uid=`mae_role`.uid and `mae_role`.shop_id=`mae_shop`.id and `mae_shop`.city_id=`mae_city`.id and `mae_shop`.admin_id=`mae_user`.id and `mae_sell`.create_time >= '.$stime.' and `mae_sell`.create_time <= '.$etime.' GROUP BY `mae_shop`.name ORDER BY count DESC');
	
	$shop_id=array();
	foreach($res as $k=>$v){
		$shop_id[]=$v['sid'];
		$shop_old=$db->ig2_want('mae_shop_orginal_unique','tid='.$v['stid']);
		$str.='
			<tr>
				<td>'.($k+1).'</td>
				<td>'.$shop_old['code'].'</td>
				<td>'.$v['cname'].'</td>
				<td>'.$v['sname'].'</td>
				<td>'.$v['uname'].'</td>
				<td>'.$v['uphone'].'</td>
				<td>'.$v['uaddress'].'</td>
				<td>'.$v['count'].'</td>
			</tr>
		';
	}
	
	$i=1;
	$count_res=count($res);
	$shop_where='is_activate=1';
	$shop_where.=$shop_id?(' and id not in('.implode(',',$shop_id).')'):'';
	foreach($db->ig2_select('mae_shop',$shop_where) as $list){
		$city=$db->ig2_want('mae_city','id='.$list['city_id']);
		$admin=$db->ig2_want('mae_user','id='.$list['admin_id']);
		
		$str.='
			<tr>
				<td>'.($count_res+$i).'</td>
				<td>'.$city['name'].'</td>
				<td>'.$list['name'].'</td>
				<td>'.$admin['name'].'</td>
				<td>'.$admin['phone'].'</td>
				<td>'.$admin['address'].'</td>
				<td>0</td>
			</tr>
		';
		$i++;
	}
	
	$str.='</table>';
	echo $str;
}

//消费者礼包
if($_GET['t']==10){
	$where='1';
	if($_GET['all']=='yes'){
		$filename='消费者礼包(全部)列表';
	}else{
		$filename='消费者礼包(未发货)列表';
		$where.=' and is_delivery=0';
	}
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$i=1;
	$where.=' order by id asc';
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>店铺编码</th>
				<th>店铺名称</th>
				<th>店长</th>
				<th>手机号</th>
				<th>地址</th>
				<th>兑换数量</th>
				<th>兑换类型</th>
				<th>状态</th>
				<th>兑换时间</th>
			</tr>
	';
	foreach($db->ig2_select('mae_shop_admin_exchange_list',$where) as $list){
		$shop=$db->ig2_want('mae_shop','id='.$list['shop_id']);
		$shop_old=$db->ig2_want('mae_shop_orginal_unique','tid='.$shop['tid']);
		$admin=$db->ig2_want('mae_user','id='.$list['uid']);
		switch($list['is_delivery']){
			case 0 : $is_delivery='未发货'; break;
			case 1 : $is_delivery='已发货'; break;
		}
		switch($list['type']){
			case 0 : $type='未选择'; break;
			case 1 : $type='洗漱包'; break;
			case 2 : $type='自拍杆'; break;
		}
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.$shop_old['code'].'</td>
				<td>'.$shop['name'].'</td>
				<td>'.$admin['name'].'</td>
				<td>'.$admin['phone'].'</td>
				<td>'.$admin['address'].'</td>
				<td>'.$list['number'].'</td>
				<td>'.$type.'</td>
				<td>'.$is_delivery.'</td>
				<td>'.(date('Y-m-d H:i:s',$list['create_time'])).'</td>
			</tr>
		';
		$i++;
	}
	
	$str.='</table>';
	echo $str;
}
//心愿单执行后台导出
if($_GET['t']==11){
	
	$filename='心愿单执行列表';
	header('Content-Disposition: filename='.$filename.'.xls');
	
	$where='1';
	$stime=$_GET['sdate']?strtotime($_GET['sdate']):strtotime(date('Y-m-d',time()));
	$etime=$_GET['edate']?(strtotime($_GET['edate'])+86400):(strtotime(date('Y-m-d',time()))+86400);
	$where.=$_GET['status']?(' and status = '.$_GET['status']):(' and status > 0');
	$where.=(' and create_time >= '.$stime.' and create_time < '.$etime);
	$where.=' order by id asc';
	
	$str='
		<table>
			<tr>
				<th>序号</th>
				<th>日期</th>
				<th>城市</th>
				<th>姓名</th>
				<th>电话</th>
				<th>内容</th>
				<th>状态</th>
			</tr>
	';
	
	$i=1;
	foreach($db->ig2_select('mae_wish_list',$where) as $list){
		$user=$db->ig2_want('mae_user','id='.$list['uid']);
		$role=$db->ig2_want('mae_role','uid='.$list['uid']);
		$shop=$db->ig2_want('mae_shop','id='.$role['shop_id']);
		$city=$db->ig2_want('mae_city','id='.$shop['city_id']);
		
		switch($list['status']){
			case 1 : $status='未实现'; break;
			case 2 : $status='已实现(待评价)'; break;
			case 3 : $status='已评价'; break;
			default : break;
		}
		
		$str.='
			<tr>
				<td>'.$i.'</td>
				<td>'.(date('Y-m-d',$list['create_time'])).'</td>
				<td>'.($city?$city['name']:'该用户已被删除').'</td>
				<td>'.($user?$user['name']:'该用户已被删除').'</td>
				<td>'.($user?$user['phone']:'该用户已被删除').'</td>
				<td>'.(trim($list['content'])?$list['content']:'-').'</td>
				<td>'.$status.'</td>
			</tr>
		';
		
		$i++;
	}
	
	$str.='</table>';
	echo $str;
}
?>